Mega Code Archive
Conversion Functions
1) Cast date to char
2) Cast decimal to integer
3) CAST examples
4) Cast null as timestamp
5) Cast number to a char
6) Cast string to date type before comparison
7) Cast table of numbers
8) Cast varchar to char
9) Cast(12 98 as number(2)) example1
10) COALESCE(state, NA)
11) Conversion Functions
12) Convert single-byte G to its multibyte representation
13) Convert that multibyte representation back to a single byte
14) Convert the string 97 13 to a number using TO_NUMBER() and then adds 25 5 to that number
15) Converts the string -$12,345 67 to a number, passing the format string $99,999 99 to TO_NUMBER()
16) Decode as If statement
17) Decode column data to output more meaningful information
18) DECODE in the GROUP BY clause
19) Demo range comparison with DECODE
20) Format Parameters
21) HEXTORAW
22) NVL Function is used if there are null values
23) NVL() converts a null to a known value
24) Nvl2(comm,12msal+comm,12msal) as yearsal
25) NVL2(x, value1, value2) returns value1 if x is not null
26) Pass column values to TRANSLATE()
27) Pass multiple search and result parameters to DECODE()
28) ROWIDTOCHAR(x)
29) ROWIDTONCHAR(x)
30) Select cast(null as date ) example3
31) SELECT CAST(NULL AS TIMESTAMP WITH LOCAL TIME ZONE) from dual
32) Select cast(oak as char(10) ) example2
33) SELECT DECODE(1, 2, 1, 3)
34) Select translate(beer bucket,beer,milk) as translate
35) Table with clob and blob column
36) The null may be made more explicit with a DECODE statement
37) TO_CHAR(0 67, B9 99)
38) TO_CHAR(0012345 6700, FM99999 99)
39) TO_CHAR(12345 67, $99,999 99)
40) TO_CHAR(12345 67, 099,999 99) (4)
41) TO_CHAR(12345 67, 99,999 99) (1)
42) TO_CHAR(-12345 67, 99,999 99) (3)
43) TO_CHAR(12345 67, 99,999 9900)
44) TO_CHAR(-12345 67, 99,999 99MI)
45) TO_CHAR(-12345 67, 99,999 99PR)
46) TO_CHAR(12345 67, 99999 99) (2)
47) TO_CHAR(12345 67, 99999 99EEEE)
48) TO_CHAR(12345 67, 99999D99)
49) TO_CHAR(12345 67, 99999G99)
50) TO_CHAR(12345 67, 99999V99)
51) TO_CHAR(12345 67, C99,999 99)
52) TO_CHAR(12345 67, L99,999 99)
53) TO_CHAR(12345 67, TM)
54) TO_CHAR(12345 67, U99,999 99)
55) TO_CHAR(2007, RN)
56) To_char(bdate,fmMonth ddth, yyyy)
57) TO_CHAR(last_ddl_time,dd-mon-yyyy hh24
58) To_char(sysdate, Day, nls_date_language=Dutch)
59) To_char(sysdate,DAY dy Dy)
60) To_char(sysdate,hh24
61) To_char(sysdate,MONTH mon) as month
62) To_char(to_date(01012006,ddmmyyyy),is on Day) as new_year_2006
63) TO_CHAR(x [, format]) converts x to a string
64) To_number(123)
65) TO_NUMBER(x [, format]) converts x to a number
66) Translate replaces a string character by character
67) Use coalesce during the table join
68) Use Date to cast string to date type
69) Use decode as if statement and output high or low
70) Use decode create dynamic select statement
71) Use Decode in table column
72) Use NVL() to convert date columns
73) Use nvl() to convert null value to 0
74) Use nvl2 to deal with null salary
75) Use the NVL() against a non-initialized BOOLEAN variable
76) Use to_char more than once to create long date format
77) Use TO_CHAR() to convert columns containing numbers to strings
78) Use TRANSLATE() to convert numbers
79) Use TRANSLATE() with column
80) Using the DECODE() Function
81) Using the TRANSLATE() Function